/**********************************************************************
* <pre>
* FILE : DealerClaimReportDao.java
* CLASS : DealerClaimReportDao
* AUTHOR : XZM
* FUNCTION : 索赔申请上报数据库操作部分：
*            [索赔申请单查询]
*            [索赔申请单上报]
*======================================================================
* CHANGE HISTORY LOG
*----------------------------------------------------------------------
* MOD. NO.| DATE     | NAME | REASON | CHANGE REQ.
*----------------------------------------------------------------------
*         |2010-06-03| XZM  | Created |
* DESCRIPTION:
* </pre>
***********************************************************************/
/**
 * $Id: DealerClaimReportDao.java,v 1.7 2011/04/12 09:51:25 xiongc Exp $
 */
package com.infodms.dms.dao.claim.dealerClaimMng;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.infodms.dms.bean.ClaimReportBean;
import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.TtAsRepairOrderPO;
import com.infodms.dms.po.TtAsWrAppauthitemPO;
import com.infodms.dms.po.TtAsWrApplicationPO;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;
import com.infoservice.po3.core.callback.DAOCallback;
import com.lowagie.tools.concat_pdf;

/**
 * 索赔申请单上报-DAO
 * @author XZM
 */
public class DealerClaimReportDao extends BaseDao{

    /** 索赔申请单表别名 */
	public String TT_AS_WR_APPLICATION = "A";
	/** 经销商表别名 */
	public String TM_DEALER = "B";
	
	/*
	 * (non-Javadoc)
	 * @see com.infodms.dms.dao.common.BaseDao#wrapperPO(java.sql.ResultSet, int)
	 */
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		return null;
	}
	
	/**
	 * 查询索赔申请单信息
	 * @param crBean 查询条件
	 * @param curPage 当前页
	 * @param pageSize 每页记录数
	 * @return PageResult<Map<String,Object>> 索赔申请单信息
	 */
	public PageResult<Map<String,Object>> queryClaim(ClaimReportBean crBean,Integer curPage,Integer pageSize){
		
		StringBuilder sBuilder = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		
		//初始化索赔申请单查询条件
		this.createWhereMap("CLAIM_NO","LIKE" ,crBean.getClaimNo() , sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("RO_NO" ,"LIKE" ,crBean.getRoNo() , sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("LINE_NO","=" ,crBean.getLineNo(), sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("CLAIM_TYPE","=" ,crBean.getClaimType() , sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("CREATE_DATE", ">=" ,crBean.getApplyStartDate(), sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("CREATE_DATE","<=" ,crBean.getApplyEndDate() , sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("STATUS", "=" ,crBean.getClaimStatus() , sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("DEALER_ID" ,"=" ,crBean.getDealerCode(), sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		this.createWhereMap("OEM_COMPANY_ID" ,"=" ,crBean.getCompanyId().toString(), sBuilder, paramList,2,"",this.TT_AS_WR_APPLICATION);
		
		String whereMap = sBuilder.toString();
		
		PageResult<Map<String, Object>> result = null;
		String sql = "SELECT a.gross_credit,A.CLAIM_NO,B.DEALER_CODE,B.DEALER_SHORTNAME DEALER_NAME,A.RO_NO AS RO_NO,A.LINE_NO,A.CLAIM_TYPE," +
				        " NVL(A.SUBMIT_TIMES,0) SUBMIT_TIMES,A.VIN,TO_CHAR(A.CREATE_DATE,'YYYY-MM-DD') CREATE_DATE," +
					    " A.STATUS,A.ID"+
					    " FROM TT_AS_WR_APPLICATION A,TM_DEALER B "+
					    " WHERE 1=1" +
					    " AND A.DEALER_ID = B.DEALER_ID" +
					    whereMap +
					    GetVinUtil.getVins(crBean.getVin(), this.TT_AS_WR_APPLICATION) +
					    " ORDER BY A.ID DESC";
		
		result = (PageResult<Map<String, Object>>)this.pageQuery(sql,
																paramList,
																this.getClass().getName()+".queryClaim()",
																pageSize,
																curPage);
		return result;
	}

	/**
	 * 上报索赔申请单
	 * @param conditionPO 更新条件
     * @return int 更新记录数
	 */
    public int reportClaim(TtAsWrApplicationPO conditionPO){
    	
    	String sql = "UPDATE TT_AS_WR_APPLICATION"+
						" SET STATUS = ?,"+
						" UPDATE_BY = ?,"+
						" UPDATE_DATE = ?,"+
						" REPORTER = ?,"+
						" REPORT_DATE = ?,"+
						" SUBMIT_TIMES = NVL(SUBMIT_TIMES,0)+1"+
						" WHERE ID = ?";
    	List<Object> paramList = new ArrayList<Object>();
    	paramList.add(conditionPO.getStatus());
    	paramList.add(conditionPO.getUpdateBy());
    	paramList.add(conditionPO.getUpdateDate());
    	paramList.add(conditionPO.getReporter());
    	paramList.add(conditionPO.getReportDate());
    	paramList.add(conditionPO.getId());
    	
    	return this.update(sql, paramList);
    }
    
    /**
     * 修改工单状态
     * @param roNo 工单号
     * @param status 工单对应索赔单是否上报(0:未上报，1:已上报)
     */
    public void modifyWorkOrderStatus(String roNo,String status,Long userId){
    	
    	if(!Utility.testString(roNo))
    		return;
    	TtAsRepairOrderPO conditionPO = new TtAsRepairOrderPO();
    	conditionPO.setRoNo(roNo);
    	TtAsRepairOrderPO orderPO = new TtAsRepairOrderPO();
    	orderPO.setRoStatus(Integer.parseInt(status));
    	orderPO.setUpdateBy(userId);
    	orderPO.setUpdateDate(new Date());
    	
    	this.update(conditionPO, orderPO);
    }
    
	/**
	 * 拼查询条件，如果页面查询过来不为空，则拼装到查询条件中
	 * @param param 参数列 对应数据库中字段
	 * @param value 参数值
	 * @param oper 操作符
	 * @param sBuilder 拼装条件容器
	 * @param paramList 参数列表
	 * @param dataType 数据类型
	 *        1 : 时间
	 *        2 ：其他
	 * @param dataFormat 数据格式，现在只有时间类型需要添加格式
	 * @param table 标明表名或别名
	 * @return
	 */
	private void createWhereMap(String param,String oper,String value,
			StringBuilder sBuilder,List<Object> paramList,int dataType,
			String dataFormat,String table){
		if(Utility.testString(value)){
			param = table + "." + param;
			if(dataType==1) {//时间
				sBuilder.append(" and ").append(param).append(" ")
				.append(oper).append(" TO_DATE(" +"?" + ",'" + dataFormat + "')");
				paramList.add(value);
			}else{//其他
				sBuilder.append(" and ").append(param).append(" ").append(oper).append(" ?");
				if("LIKE".equalsIgnoreCase(oper)){//模糊查询
					paramList.add("%" +value +"%");
				}else{
					paramList.add(value);
				}
			}
		}
	}
	/****
	 * 
	 * 查询该车是否重复做首保
	 * @param id
	 * @param expStatus
	 * @return
	 */
    public List<Map<String,Object>> checkModeMaintain2(String id,String expStatus){
    	StringBuilder sql= new StringBuilder();
    	sql.append("SELECT count(1) as COUNT\n" );
    	sql.append("FROM TT_AS_WR_APPLICATION A\n" );
    	sql.append("WHERE 1=1\n" );
    	sql.append("AND A.STATUS NOT IN ("+expStatus+")\n" );
    	sql.append("AND A.CLAIM_TYPE = "+Constant.CLA_TYPE_02+"\n" );
    	sql.append("and a.free_m_amount=1");
    	sql.append("and a.id not in (?)");
    	sql.append("AND A.VIN in (select vin from TT_AS_WR_APPLICATION where id=?)");
    	
    	List<Object> paramList = new ArrayList<Object>();
    	paramList.add(id);
    	paramList.add(id);
    	return this.pageQuery(sql.toString(), paramList, this.getFunName());
    }
    /******
     * 根据索赔单ID 查询出车型 和 配置 状态 
     * @param claimId
     * @return
     */
    public List<Map<String,Object>> viewApplication(String claimId){
    	StringBuffer sql= new StringBuffer();
    	sql.append("select substr(mg.group_code,\n" );
    	sql.append("              instr(mg.group_code, '.', 1, 1) + 1,\n" );
    	sql.append("              length(mg.group_code)) as model_status,a.claim_type,\n" );
    	sql.append("       a.model_code\n" );
    	sql.append("  from Tt_As_Wr_Application a, tm_vehicle v, tm_vhcl_material_group mg\n" );
    	sql.append(" where a.id = ?\n" );
    	sql.append("   and a.vin = v.vin\n" );
    	sql.append("   and v.package_id = mg.group_id");
    	List<Object> paramList = new ArrayList<Object>();
    	paramList.add(claimId);
    	return this.pageQuery(sql.toString(), paramList, this.getFunName());
    }
    
    public List<Map<String,Object>> viewPartType(String claimId){
    	StringBuffer sql= new StringBuffer();
    	sql.append("select p.part_code,p.wr_labourcode,ppt.parttype_code from Tt_As_Wr_Partsitem p,tm_pt_part_base pb,tm_pt_part_type ppt  where p.id=?\n" );
    	sql.append("and  p.part_code = pb.part_code\n" );
    	sql.append("and pb.part_type_id = ppt.id(+)");
    	List<Object> paramList = new ArrayList<Object>();
    	paramList.add(claimId);
    	return this.pageQuery(sql.toString(), paramList, this.getFunName());
    }
    
    public List<Map<String,Object>> viewAuditing(String modelCode,String modelStatus,String labourCode ,String partTypeCode,String partCode){
        StringBuffer sql= new StringBuffer();
        sql.append("SELECT COUNT(1) AS COUNT, MAX(R.REMARK) AS REMARK\n" );
        sql.append("  FROM tm_pt_claim_rule R\n" );
        sql.append(" WHERE (R.MODE_CODE IS NULL OR R.MODE_CODE = '"+modelCode+"')\n" );
        sql.append("   AND (R.MODEL_STATUS IS NULL OR R.MODEL_STATUS = '"+modelStatus+"')\n" );
        sql.append("   AND (R.LABOUR_CODE IS NULL OR R.LABOUR_CODE = '"+labourCode+"')\n" );
        sql.append("and (r.part_code is null or r.part_code='"+partCode+"')");
        sql.append("   AND (R.PART_TYPE_CODE IS NULL OR R.PART_TYPE_CODE = '"+partTypeCode+"')\n" );
        sql.append(" GROUP BY R.REMARK");
    	return this.pageQuery(sql.toString(), null, this.getFunName());
    }


    
    
    
    
	/****
	 * 
	 * 查询该车是否重复做首保
	 * @param id
	 * @param expStatus
	 * @return
	 */
    public List<TtAsWrApplicationPO> viewClaimType(String id){
    	StringBuilder sql= new StringBuilder();
    	sql.append("SELECT claim_type as claim_type\n" );
    	sql.append("FROM TT_AS_WR_APPLICATION A\n" );
    	sql.append("WHERE 1=1\n" );
    	sql.append("and id= ?");
    	
    	List<Object> paramList = new ArrayList<Object>();
    	paramList.add(id);
    	return this.select(TtAsWrApplicationPO.class, sql.toString(), paramList);
    }
    
    //重复做首保将状态置为拒绝
    public void updateClaimStatus (String id){
    	StringBuilder sql= new StringBuilder();
    	Date date = new Date();
    	 sql.append("update tt_as_wr_application set status="+Constant.CLAIM_APPLY_ORD_TYPE_05+",auditing_date=sysdate where id=?" );
    	List<Object> paramList = new ArrayList<Object>();
        paramList.add(id);
        this.update(sql.toString(), paramList);
    }
    public void InsertClaimAudit(String claimId,Integer status){
    	TtAsWrAppauthitemPO appAuthPO = new TtAsWrAppauthitemPO();
		appAuthPO.setId(Long.parseLong(claimId));
		appAuthPO.setApprovalPerson("自动审核"+" [授权审核]");//自动审核 审核人员 固定为100
		appAuthPO.setApprovalLevelCode("100");//自动审核 授权角色 固定为100
		appAuthPO.setApprovalDate(new Date());
		appAuthPO.setApprovalResult(String.valueOf(status));//授权结果=索赔申请单状态
		appAuthPO.setRemark("该车辆存在首保，不能再做首保");//备注=审核不通过理由
		appAuthPO.setCreateBy(new Long(-1));
		appAuthPO.setCreateDate(new Date());
		this.insert(appAuthPO);
		
    }
    public void InsertClaimAudit2(String claimId,Integer status,String remark){
    	TtAsWrAppauthitemPO appAuthPO = new TtAsWrAppauthitemPO();
		appAuthPO.setId(Long.parseLong(claimId));
		appAuthPO.setApprovalPerson("自动审核"+" [授权审核]");//自动审核 审核人员 固定为100
		appAuthPO.setApprovalLevelCode("100");//自动审核 授权角色 固定为100
		appAuthPO.setApprovalDate(new Date());
		appAuthPO.setApprovalResult(String.valueOf(status));//授权结果=索赔申请单状态
		appAuthPO.setRemark(remark);//备注=审核不通过理由
		appAuthPO.setCreateBy(new Long(-1));
		appAuthPO.setCreateDate(new Date());
		this.insert(appAuthPO);
		
    }
}
